#coding=utf-8
import os, datetime

from sqlalchemy.orm import *
from sqlalchemy import *

from stockdb.misc import *
from stconfig import config_dir

gui_sess = scoped_session(sessionmaker(autoflush=True, transactional=True))

metadata = MetaData()
colattrs = Table('colattrs', metadata,
    Column('id', Integer, primary_key = True),
    Column('name', String(20), unique = True),
    
    Column('colhead', String(20), nullable=False),
    Column('pytype', String(20), nullable=False),
    Column('gtype', String(20), nullable=False),
    
    Column('is_percent', Boolean, default=False),
    Column('is_colored', Boolean, default=False)
    )

stgroups = Table('stgroups', metadata,
    Column('id', Integer, primary_key = True),
    Column('name', String(40), unique = True),
    Column('created_at', Date , default=datetime.date.today),
    Column('note', String(200)),
    )

mystocks = Table('mystocks', metadata,
    Column('id', Integer, primary_key = True),
    Column('code', String(10), unique = True),
    Column('name', String(40)),
    )

group_stock = Table('group_stock', metadata,   
    Column('group_id', Integer, ForeignKey('stgroups.id')),
    Column('stock_id', Integer, ForeignKey('mystocks.id')),
    )

pricepoints = Table('pricepoints', metadata,
    Column('id', Integer, primary_key = True),
    Column('stock_id', Integer, ForeignKey('mystocks.id')),
    Column('value', Float),
    )    
        
fn = os.path.join(config_dir, 'gui.sqlite')
db_uri = 'sqlite:///%s'%fn
engine = create_engine(db_uri)
metadata.create_all(engine)

class ColAttr(object):
    def __init__(self, name, head, pytype, gtype, is_percent=False, is_colored=False):
        self.name = name
        self.colhead = head
        
        self.pytype = pytype
        self.gtype = gtype
        
        self.is_percent = is_percent
        self.is_colored = is_colored

class StGroup(object):
    def __init__(self, name = None):
        if name:
            self.name = name
            
    def get_codes(self):    
        return [ s.code for s in self.stocks ]
        
    def add_stocks(self,  *codes ):
        
        for c in codes:
            #print c
            s = find_stock(c)
            self.stocks.append( MyStock(c, s.name))
            
        gui_sess.commit()
        
    def remove_stock(self, code):
    
        ms = gui_sess.query(MyStock).filter_by(code=code).one()
        for pp in ms.pps:
            gui_sess.delete(pp)
        gui_sess.delete(ms)
            
        gui_sess.commit()
    
    def get_mystock(self, code):
    
        for s in self.stocks:
            if s.code == code:
                return s
                        
        return None
                    
class MyStock(object):
    
    def __init__(self, code, name):
        
        self.code = code
        self.name = name
        
    def add_pps(self, *vs):
    
        for v in vs:
            self.pps.append(PricePoint(v))
        
        gui_sess.commit()
    
    def remove_pps(self):
        for pp in self.pps:
            gui_sess.delete(pp)
        gui_sess.commit()
    
    def remove_pp(self, value):
    
        p = gui_sess.query(PricePoint).\
                filter_by(stock_id=self.id).\
                filter_by(value=value).one()
        
        gui_sess.delete(p)
        gui_sess.commit()
                 
        
class PricePoint(object):
    
    def __init__(self, value):
        self.value = value  
    

mapper(ColAttr, colattrs)

mapper(StGroup, stgroups, 
    properties = {'stocks': relation(MyStock, secondary=group_stock, backref='groups')}
    )

mapper(MyStock, mystocks,
    properties = {'pps': relation(PricePoint, 
                         backref=backref('stock'))}
    )
mapper(PricePoint, pricepoints)

gui_sess.configure(bind=engine)

def find_colattr(name):
    return gui_sess.query(ColAttr).filter_by(name = name).one()

def get_stgroups():
    return gui_sess.query(StGroup).order_by(stgroups.c.created_at).all()

def create_stgroup(name):
    sg = StGroup(name)
    
    gui_sess.save(sg)
    gui_sess.commit()
    
    return sg

def find_mystock(code):
    return gui_sess.query(MyStock).filter_by(code=code).one()
    
'''
for ms in sg.stocks:
    c = ms.code
    s = find_stock(c)
    td = s.latest_td
    N = td.N
    vs = [td.close-N, td.close-0.5*N, td.close+0.5*N, td.close+N]
    ms.add_pps(*vs)

'''            
if __name__ == '__main__':
    cas = [ ColAttr('date', '交易日', 'str', 'gchararray'),
            
            ColAttr('name', '名称', 'str', 'gchararray'),
            ColAttr('code', '代码', 'str', 'gchararray'),
            
            ColAttr('open', '开盘价', 'float', 'gdouble'),
            ColAttr('close', '收盘价', 'float','gdouble'),
            ColAttr('highest', '最高价', 'float', 'gdouble'),
            ColAttr('lowest', '最低价', 'float', 'gdouble'),
            
            ColAttr('change', '涨幅', 'float', 'gdouble', True, True),
            ColAttr('jump', '跳空?', 'float', 'gdouble', True, True),
            
            ColAttr('interval', '波动区间', 'str', 'gchararray'),
            ColAttr('fluctuation', '波幅', 'float', 'gdouble', True),
            
            ColAttr('volume', '成交量(股)', 'int', 'gint'),
            ColAttr('amount', '成交额(元)', 'int', 'gint'),
            ColAttr('trade_rate', '换手率', 'float', 'gdouble', True),
            ColAttr('time', '时间', 'str', 'gchararray'),
            ColAttr('now', '现价', 'float', 'gdouble'), 
    ]
    
    for ca in cas:
        gui_sess.save(ca)
    
    gui_sess.commit()
